﻿--8 - Найти родителя у которого больше всего контактов

select MAX(hr.c) from
(
select [fk_PersonID], Count(*) c from [MyFamily].[dbo].[Contact]
where [fk_PersonID]
in
(
select distinct  A1.[ID]
from [MyFamily].[dbo].[Persons] as A1, [MyFamily].[dbo].[Persons] as A2
where ((A1.ID = A2.fk_motherID) or (A1.ID = A2.fk_fatherID))
)
GROUP BY fk_PersonID
) hr



select top 1 [ID], [LastName], [Name], a.c ac
from [MyFamily].[dbo].[Persons]
 join
 (select [fk_PersonID], Count(*) c from
  [MyFamily].[dbo].[Contact] group by [fk_PersonID]) a
  on ([MyFamily].[dbo].[Persons].ID = a.fk_PersonID)
where [ID] in (
select distinct  A1.[ID]
from [MyFamily].[dbo].[Persons] as A1, [MyFamily].[dbo].[Persons] as A2
where ((A1.ID = A2.fk_motherID) or (A1.ID = A2.fk_fatherID))
)
